An Ad Hoc Youtube Playlist Player Gadget, Via Google Spreadsheets

A tweet from Keir Clarke from Google Maps Mania last week tipped me off to this post from maps evangelist Pamela Fox – Geocoding with Google Spreadsheets (and Gadgets) – in which she demonstrates how to improve the spreadsheets to maps workflow using a Google spreadsheet gadget.

I’d actually been thinking about using a mapplet in the maps environment, (rather than a gadget in the spreadsheets environment) to do something similar, so it was great to see how someone else had set about tackling the matter :-)

Anyway, a quick look through the spreadsheet gadgets tutorial convinced me it should be easy enough to create a gadget that could act as a video playlist player for a set of Youtube movie URLs listed in a spreadsheet. I already had some gadget code that I guessed may be reusable, and it turned out it was (Google Gadgets – RSS Feed Powered YouTube Playlist Player).

Here’s a demo:

Highlight the list of cells and include the custom gadget URL for the player: http://hosting.gmodules.com/ig/gadgets/file/100510412849522254945/videoPlaylistSpreadsheetNew.xml?nocache.

As to where the list of videos came from? I scraped them from a webpage that included lots of embedded videos (i.e. a webpage that was essentially an ad hoc video playlist). A quick peak at the source of a candidate page showed me where I could find the URLs:

If we now load this page in to a Google spreadsheet using the =importXML formula (not the =importHTML formula), we can use an XPATH expression to pull out all the movie URLs from the page.

Here’s the expression you need:
//object/param[@name=’movie’]/@value

For a couple of examples, see this how to scrape a list of Youtube movies from a webpage using Google spreadsheets and view them in a Google gadget.